/****** Object:  Stored Procedure AdjustQuantityGetList    Script Date: Wednesday, October 24, 2007 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spAdjustQuantityGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spAdjustQuantityGetList]
GO

	
	
	
/******************************************************************************
**		File: 
**		Name: spAdjustQuantityGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 10/24/2007 8:04:06 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE spAdjustQuantityGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempAdjustQuantity (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	AdjustQuantityID int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempAdjustQuantity ([AdjustQuantityID]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [AdjustQuantityID] FROM [AdjustQuantity]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [AdjustQuantity]

SELECT
	[AdjustQuantity].[AdjustQuantityID],
	[Amount],
	[AdjustedDate],
	[Comment],
	[BulkOrderID],
	[AdjustedByUserID],
	[POID]
FROM
	#TempAdjustQuantity AS tblTemp JOIN [AdjustQuantity] ON
	tblTemp.AdjustQuantityID = [AdjustQuantity].AdjustQuantityID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempAdjustQuantity

GO

	
